/**
 * Copyright (c) 2005-2016, yinwer81 (yinwer81@aliyun.com)
 *
 * Licensed under the Apache License, Version 2.0
 */
package com.nozturn.throne.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;

import com.nozturn.throne.dao.AuthorizationDao;
import com.nozturn.throne.entity.Authorization;
import com.nozturn.throne.entity.OAuthInfoDTO;

@Repository
public class AuthorizationDaoImpl implements AuthorizationDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Authorization createAuthorization(final Authorization authorization) {
        final String sql = "insert into tbl_authorization(authorization_code, client_pk, user_pk, valid, created_at, updated_at, created_by, updated_by) values(?,?,?,?,now(),now(),?,?)";

        final GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement psst = connection.prepareStatement(sql, new String[] { "pk" });
                int count = 1;
                psst.setString(count++, authorization.getAuthorizationCode());
                psst.setLong(count++, authorization.getClientPk());
                psst.setLong(count++, authorization.getUserPk());
                psst.setInt(count++, authorization.getValid());
                psst.setString(count++, authorization.getCreatedBy());
                psst.setString(count++, authorization.getUpdatedBy());
                return psst;
            }
        }, keyHolder);

        authorization.setPk(keyHolder.getKey().longValue());
        return authorization;
    }

    public int updateAuthorization(final Authorization authorization) {
        final String sql = "update tbl_authorization set valid=?, updated_at=now(), updated_by=? where valid=200 and pk=?";
        int updated = jdbcTemplate.update(sql, authorization.getValid(), authorization.getUpdatedBy(), authorization.getPk());
        return updated;
    }

    public int deleteAuthorization(final String authorizationCode) {
        final String sql = "update tbl_authorization set valid=100, updated_at=now(), updated_by='system' where valid=200 and pk=?";
        int updated = jdbcTemplate.update(sql, authorizationCode);
        return updated;
    }

    public Authorization findOne(final Long pk) {
        final String sql = "select pk, authorization_code, client_pk, user_pk, valid, created_at, updated_at, created_by, updated_by from tbl_authorization where pk = ? and valid=200";
        final List<Authorization> authorizationList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Authorization>(Authorization.class),
                pk);
        if (authorizationList.size() == 0) {
            return null;
        }
        return authorizationList.get(0);
    }

    public Authorization findByAuthorizationCode(final String authorizationCode) {
        final String sql = "select pk, authorization_code, client_pk, user_pk, valid, created_at, updated_at, created_by, updated_by from tbl_authorization where authorization_code = ? and valid=200";
        final List<Authorization> authorizationList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Authorization>(Authorization.class),
                authorizationCode);
        if (authorizationList.size() == 0) {
            return null;
        }
        return authorizationList.get(0);
    }

    public Authorization findByUserPkAndClientPk(final Long userPk, final Long clientPk) {
        final String sql = "select pk, authorization_code, client_pk, user_pk, valid, created_at, updated_at, created_by, updated_by from tbl_authorization where user_pk=? and client_pk = ? and valid=200";
        final List<Authorization> authorizationList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Authorization>(Authorization.class),
                userPk, clientPk);
        if (authorizationList.size() == 0) {
            return null;
        }
        return authorizationList.get(0);
    }

    public List<Authorization> findByAuthorization(final Authorization authorization) {
        final String sql = "select pk, authorization_code, client_pk, user_pk, valid, created_at, updated_at, created_by, updated_by from tbl_authorization where valid=200 and (? is null or pk = ?) and (? is null or authorization_code like ?)";
        final List<Authorization> authorizationList = new ArrayList<Authorization>();
        jdbcTemplate.query(sql, new Object[] { authorization.getPk(), authorization.getPk(), authorization.getAuthorizationCode(),
                authorization.getAuthorizationCode() }, new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        Authorization authorization = new Authorization();
                        authorization.setPk(rs.getLong("pk"));
                        authorization.setAuthorizationCode(rs.getString("authorization_code"));
                        authorization.setUserPk(rs.getLong("client_pk"));
                        authorization.setClientPk(rs.getLong("user_pk"));
                        authorization.setValid(rs.getInt("valid"));
                        authorization.setCreatedAt(rs.getDate("created_at"));
                        authorization.setCreatedBy(rs.getString("created_by"));
                        authorization.setUpdatedAt(rs.getDate("updated_at"));
                        authorization.setUpdatedBy(rs.getString("updated_by"));

                        authorizationList.add(authorization);
                    }
                });
        return authorizationList;
    }

    public OAuthInfoDTO findAllByAuthorizationCode(final String authorizationCode) {
        final String sql = "select tat.pk access_token_pk, tat.access_token, tat.authorization_pk, tat.created_at, tat.expires_in, tat.valid access_token_valid , "
                + "ta.pk authorization_pk, ta.authorization_code, ta.valid authorization_valid, tu.pk user_pk, tu.username, tu.usertype, "
                + "tu.valid user_valid, tc.pk client_pk, tc.client_name, tc.client_id,tc.client_secret, tc.valid client_valid from "
                + " tbl_authorization ta left join tbl_access_token tat on tat.authorization_pk = ta.pk left join tbl_user tu on"
                + " ta.user_pk = tu.pk left join tbl_client tc on ta.client_pk = tc.pk where ta.authorization_code = ? and ta.valid = 200 and tc.valid = 200 and tu.valid=200";
        final List<OAuthInfoDTO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<OAuthInfoDTO>(OAuthInfoDTO.class),
                authorizationCode);
        if (list.size() == 0) {
            return null;
        }
        return list.get(0);
    }

}
